package DBController;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class DBConnector {
	private static DBConnector connector_ = null;
	public static DBConnector getInstance() throws Exception {
		if (connector_ == null) {
			connector_ = new DBConnector();
		}
		return connector_;
	}
	public Connection getConnection() throws Exception {
		// Get DataSource
		Context ctx = new InitialContext();
		DataSource ds = (DataSource) ctx
				.lookup("java:comp/env/jdbc/mydatabase");
		Connection c = ds.getConnection();
		return c;
	}
	public List<city> getCities() throws Exception {
		List<city> temp = new ArrayList<city>();
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			ResultSet res = st.executeQuery("SELECT * FROM cities");
			while (res.next()) {
				city cityObj = new city();
				cityObj.setCityID(Integer.parseInt(res.getString("CityID")));
                cityObj.setCityName(res.getString("CityName"));
				temp.add(cityObj);
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp;
	}
	public List<district> getDistricts(int cityID) throws Exception {
		List<district> temp = new ArrayList<district>();
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			ResultSet res = st.executeQuery("SELECT * FROM districts where cityID = " + cityID);
			while (res.next()) {
				district districtObj = new district();
				districtObj.setDistrictID(Integer.parseInt(res.getString("districtID")));
				districtObj.setCityID(Integer.parseInt(res.getString("cityID")));
				districtObj.setDistrictName(res.getString("districtName"));
				temp.add(districtObj);
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp;
	}
	public List<country> getCountries() throws Exception {
		List<country> temp = new ArrayList<country>();
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			ResultSet res = st.executeQuery("SELECT * FROM countries");
			while (res.next()) {
				country countryObj = new country();
				countryObj.setId(Integer.parseInt(res.getString("id")));
				countryObj.setName(res.getString("name"));
				temp.add(countryObj);
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp;
	}
	public DBController.hostFamily getHostFamilyByUserID(int hostUserID) throws Exception{
		DBController.hostFamily temp = new DBController.hostFamily();
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			ResultSet res = st.executeQuery("SELECT * FROM hostfamilies WHERE hostUserID = " + hostUserID);
			while (res.next()) {
				temp.setHostFamilyID(Integer.parseInt(res.getString(1)));
				temp.setHostUserID(Integer.parseInt(res.getString(2)));
				temp.setDistrictID(Integer.parseInt(res.getString(3)));
				temp.setPublicTransportDistance(Double.parseDouble(res.getString(4)));
				temp.setMinStayDay(Integer.parseInt(res.getString(5)));
				temp.setMaxStayDay(Integer.parseInt(res.getString(6)));
				temp.setNumberOfAdult(Integer.parseInt(res.getString(7)));
				temp.setNumberOfChildren(Integer.parseInt(res.getString(8)));
				temp.setIsSmokingAllowed(res.getString(9));
				temp.setIsMealProvided(res.getString(10));
				temp.setPricePerMeal(Double.parseDouble(res.getString(11)));
				temp.setIsPetHeld(res.getString(12));
				temp.setIsInternetProvided(res.getString(13));
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp;
	}
	public DBController.guestProfile getGuestProfileByUserID(int guestUserID) throws Exception{
		DBController.guestProfile temp = new DBController.guestProfile();
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			ResultSet res = st.executeQuery("SELECT * FROM guestsprofiles WHERE guestUserID = " + guestUserID);
			while (res.next()) {
				temp.setGuestProfileID(Integer.parseInt(res.getString(1)));
				temp.setGuestUserID(Integer.parseInt(res.getString(2)));
				temp.setArrivalDate(res.getTimestamp(3));
				temp.setDepartureDate(res.getTimestamp(4));
				temp.setDistrictID(Integer.parseInt(res.getString(5)));
				temp.setNumberOfGuests(Integer.parseInt(res.getString(6)));
				temp.setMaxPrice(Double.parseDouble(res.getString(7)));
				temp.setIsMealRequired(res.getString(8));
				temp.setIsSmokingAllowed(res.getString(9));
				temp.setIsInternetRequired(res.getString(10));
				temp.setTypeOfJourney(res.getString(11));
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp;
	}
	public DBController.guestTrip getGuestTripByUserID(int guestUserID) throws Exception{
		DBController.guestTrip temp = new DBController.guestTrip();
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			ResultSet res = st.executeQuery("SELECT * FROM guestTrip WHERE guestUserID = " + guestUserID);
			while (res.next()) {
				temp.setGuestTripID(Integer.parseInt(res.getString(1)));
				temp.setGuestUserID(Integer.parseInt(res.getString(2)));
				temp.setDistrictID(Integer.parseInt(res.getString(3)));
				temp.setArrivalDate(res.getTimestamp(4));
				temp.setDepartureDate(res.getTimestamp(5));
				temp.setNumberOfGuests(Integer.parseInt(res.getString(6)));
				temp.setMaxPrice(Double.parseDouble(res.getString(7)));
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp;
	}
	public int getCityIDByDistrictID(int districtID) throws Exception{
		int temp = 1;
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			ResultSet res = st.executeQuery("SELECT * FROM districts WHERE districtID = " + districtID);
			while (res.next()) {
				temp = Integer.parseInt(res.getString("cityID"));
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp;
	}
	public boolean isUserNameExisted(String userName) throws Exception{
		boolean temp = true;
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			Statement st2 = con.createStatement();
			String SQLString = "SELECT * FROM hostusers WHERE account = '" + userName +"'";
			String SQLString2 = "SELECT * FROM guestusers WHERE account = '" + userName +"'";	
			ResultSet res = st.executeQuery(SQLString);
			ResultSet res2 = st2.executeQuery(SQLString2);
			boolean hasRow = res.next();
			boolean hasRow2 = res2.next();
			if(!hasRow && !hasRow2) temp=false;
			else temp=true;
			con.close();
		}
		catch (SQLException s) {
			return true;
		}
		return temp;
	}
	public boolean isEmailExisted(String email) throws Exception{
		boolean temp = true;
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			Statement st2 = con.createStatement();
			String SQLString = "SELECT * FROM hostusers WHERE emailAddress = '" + email +"'";
			String SQLString2 = "SELECT * FROM guestusers WHERE emailAddress = '" + email +"'";	
			ResultSet res = st.executeQuery(SQLString);
			ResultSet res2 = st2.executeQuery(SQLString2);
			boolean hasRow = res.next();
			boolean hasRow2 = res2.next();
			if(!hasRow && !hasRow2) temp=false;
			else temp=true;
			con.close();
		}
		catch (SQLException s) {
			return true;
		}
		return temp;
	}
	public List<DBController.room> getAllRoom(int hostFamilyID) throws Exception{
		List<DBController.room> temp = new ArrayList<DBController.room>();
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			String SQLString = "SELECT * FROM room WHERE hostFamilyID = " + hostFamilyID;	
			ResultSet res = st.executeQuery(SQLString);
			System.out.println(SQLString);
			while (res.next()) {
				DBController.room roomObj = new DBController.room();
				roomObj.setRoomID(Integer.parseInt(res.getString(1)));
				roomObj.setRoomName(res.getString(3));
				roomObj.setRoomType(res.getString(4));
				roomObj.setRoomSize(Integer.parseInt(res.getString(5)));
				System.out.println("OK");
				if(res.getString(6) != null && res.getString(6).equals("Y"))
					roomObj.setHasInsideBathroom(res.getString(6).charAt(0));
				else
					roomObj.setHasInsideBathroom('N');
				if(res.getString(7) != null && res.getString(7).equals("Y"))
					roomObj.setHasInsideTV(res.getString(7).charAt(0));
				else
					roomObj.setHasInsideTV('N');
				if(res.getString(8) != null && res.getString(8).equals("Y"))
					roomObj.setHasInsideAirCondition(res.getString(8).charAt(0));
				else
					roomObj.setHasInsideAirCondition('N');
				if(res.getString(9) != null && res.getString(9).equals("Y"))
					roomObj.setHasInsideFridge(res.getString(9).charAt(0));
				else
					roomObj.setHasInsideFridge('N');
				System.out.println("OK1");
				roomObj.setPricePerDay(Double.parseDouble(res.getString(10)));				
				temp.add(roomObj);
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp;
	}
	public List<DBController.roomPhoto> getRoomPhoto(int roomID) throws Exception{
		List<DBController.roomPhoto> temp = new ArrayList<DBController.roomPhoto>();
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			String SQLString = "SELECT * FROM roomphotos WHERE roomID = " + roomID;	
			ResultSet res = st.executeQuery(SQLString);
			System.out.println(SQLString);
			while (res.next()) {
				DBController.roomPhoto roomPhotoObj = new DBController.roomPhoto();
				roomPhotoObj.setRoomPhotoID(Integer.parseInt(res.getString(1)));
				roomPhotoObj.setRoomID(Integer.parseInt(res.getString(2)));
				roomPhotoObj.setPhotoURL(res.getString(3));			
				temp.add(roomPhotoObj);
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp;
	}
	public DBController.room getRoom(int roomID) throws Exception{
		List<DBController.room> temp = new ArrayList<DBController.room>();
		try {
			Connection con = getConnection();
			Statement st = con.createStatement();
			String SQLString = "SELECT * FROM room WHERE roomID = " + roomID;	
			ResultSet res = st.executeQuery(SQLString);
			System.out.println(SQLString);
			while (res.next()) {
				DBController.room roomObj = new DBController.room();
				roomObj.setRoomID(Integer.parseInt(res.getString(1)));
				roomObj.setRoomName(res.getString(3));
				roomObj.setRoomType(res.getString(4));
				roomObj.setRoomSize(Integer.parseInt(res.getString(5)));
				System.out.println("OK");
				if(res.getString(6) != null && res.getString(6).equals("Y"))
					roomObj.setHasInsideBathroom(res.getString(6).charAt(0));
				else
					roomObj.setHasInsideBathroom('N');
				if(res.getString(7) != null && res.getString(7).equals("Y"))
					roomObj.setHasInsideTV(res.getString(7).charAt(0));
				else
					roomObj.setHasInsideTV('N');
				if(res.getString(8) != null && res.getString(8).equals("Y"))
					roomObj.setHasInsideAirCondition(res.getString(8).charAt(0));
				else
					roomObj.setHasInsideAirCondition('N');
				if(res.getString(9) != null && res.getString(9).equals("Y"))
					roomObj.setHasInsideFridge(res.getString(9).charAt(0));
				else
					roomObj.setHasInsideFridge('N');
				System.out.println("OK1");
				roomObj.setPricePerDay(Double.parseDouble(res.getString(10)));				
				temp.add(roomObj);
			}
			con.close();
		}
		catch (SQLException s) {
			System.out.println("SQL code does not execute.");
		}
		return temp.get(0);
	}
}
